<?php

namespace Lavender\Db;

use Lavender\Errno;
use Lavender\WebPage;

/**
 * Mysql Operater Class
 * based on mysqli
 */
class Mysql
{
	private $host;
	private $username;
	private $password;
	private $database;
	private $port;
	private $charset;
	private $db_link;

	/**
	 * the last query sql
	 * @var int
	 */
	public $last_sql = '';

	public function __construct($host, $username, $password, $database, $port = null, $charset = null)
	{
		$this->host = $host;
		$this->username = $username;
		$this->password = $password;
		$this->database = $database;
		$this->port = $port;
		$this->charset = $charset;
	}

	public function __destruct()
	{
		if ($this->db_link) {
			mysqli_close($this->db_link);
			$this->db_link = null;
		}
	}

	/**
	 * connect & select database
	 * @param bool $reconnect
	 * @throws Exception
	 */
	public function open($reconnect = false)
	{
		if ($this->db_link && !$reconnect) {
			return;
		}
		$this->db_link = mysqli_connect($this->host, $this->username, $this->password, $this->database, $this->port);
		if (!$this->db_link) {
			throw new Exception("database connect failed,code:" . mysqli_connect_errno() . ',msg:' . mysqli_connect_error(), Errno::DB_CONNECT_FAILED);
		}

		if ($this->charset) {
			$sql = "SET NAMES {$this->charset}";
			$result = mysqli_query($this->db_link, $sql);
			if (!$result) {
				throw new Exception("query failed,sql:[{$sql}],code:" . mysqli_errno($this->db_link) . ',msg:' . mysqli_error($this->db_link), Errno::DB_FAILED);
			}
		}
	}

	/**
	 * close databse link
	 */
	function close()
	{
		if ($this->db_link) {
			mysqli_close($this->db_link);
			$this->db_link = null;
		}
	}

	function get_link()
	{
		return $this->db_link;
	}

	function execute_mutil($sql){
		$this->last_sql = $sql;
		$filename = 'sql';
		$content = date('Y-m-d H:i:s') . ' - ';
		$time_1 = microtime(true);

		$this->open();
		$query_result = mysqli_multi_query($this->db_link, $sql);

		$time_2 = microtime(true) - $time_1;
		$content .= sprintf("%08.5f", $time_2) . ' - ';
		$content .= var_export($this->database, true) . " - \n";
		$content .= $sql . "\n";

		\Lavender\Core::log($filename, $content);
		$result = [];
		if ($query_result) {
			// 
			$this->close();

			// TODO
			// do {
			// 	/* store first result set */
			// 	if ($result = mysqli_store_result($this->db_link)) {				
			// 			mysqli_free_result($result);
			// 	}
			// 	if(!mysqli_more_results($this->db_link)){
			// 		break;
			// 	}
			// } while (mysqli_next_result($this->db_link));
		}
		else{
			throw new Exception('execute_mutil sql failed,code:' . mysqli_errno($this->db_link) . ',error:' . mysqli_error($this->db_link) . ',sql:[' . $sql . ']', Errno::DB_FAILED);
		}
		return $result;
	}

	/**
	 * execute sql
	 * @param string $sql
	 * @param int $retry
	 * @return bool|\mysqli_result
	 * @throws Exception
	 */
	function execute($sql, $retry = 1)
	{
		$this->last_sql = $sql;

		$filename = 'sql';

		// $content .= "ip: {$ip}\n";

		$content = date('Y-m-d H:i:s') . ' - ';
		$time_1 = microtime(true);

		$this->open();
		$result = mysqli_query($this->db_link, $sql);

		$time_2 = microtime(true) - $time_1;
		$content .= sprintf("%08.5f", $time_2) . ' - ';
		$content .= var_export($this->database, true) . ' - ';
		$content .= $sql . "\n";

		\Lavender\Core::log($filename, $content);

		if (!$result) {
			//if (mysqli_errno($this->db_link) == 1062) { //key duplicated
			//return false;
			//}

			if (mysqli_errno($this->db_link) == 2006 && $retry) {
				$this->close();
				return $this->execute($sql, --$retry);
			}

			throw new Exception('execute sql failed,code:' . mysqli_errno($this->db_link) . ',error:' . mysqli_error($this->db_link) . ',sql:[' . $sql . ']', Errno::DB_FAILED);
		}
		return $result;
	}

	/**
	 * @param array $fields
	 * @return string
	 */
	public function get_field_str(array $fields = array()){
		//check fields
		if (!empty($fields)) {
			if (!is_array($fields)) {
				throw new Exception("param fields not an array", Errno::PARAM_INVALID);
			}

			foreach ($fields as &$field) {
				if (!$this->check_name($field)) {
					throw new Exception("field \"{$field}\" invalid", Errno::PARAM_INVALID);
				}
				$field = $this->get_field_name($field);
			}

			$fields = implode(',', $fields);
		} else {
			$fields = '*';
		}
		return $fields;
	}

	/**
	 * @param string|null $group
	 * @param string|null $having
	 * @return string
	 */
	public function get_group_str($group = null, $having = null){
		$sql = '';
		if ($group) {
			if (!preg_match('/^[a-z0-9_\., `]+$/i', $group)) {
				throw new Exception("group exp \"{$group}\" is invalid", Errno::PARAM_INVALID);
			}
			$sql .= " GROUP BY {$group}";
			if($having){
				if (!preg_match('/^[a-z0-9_\., `\(\)\>\<\=]+$/i', $having)) {
					throw new Exception("having exp \"{$having}\" is invalid", Errno::PARAM_INVALID);
				}
				$sql .= " HAVING {$having}";
			}
		}
		return $sql;
	}

	/**
	 * @param string|null $order
	 * @return string
	 */
	public function get_order_str($order = null){
		$sql = '';
		if ($order) {
			if (!preg_match('/^[a-z0-9_\., `]+$/i', $order)) {
				throw new Exception("order exp \"{$order}\" is invalid", Errno::PARAM_INVALID);
			}
			$sql .= " ORDER BY {$order}";
		}
		return $sql;
	}

	/**
	 * @param int|null $offset
	 * @param int|null $length
	 * @return string
	 */
	public function get_limit_sql($offset = null, $length = null){
		$sql = '';
		if ($offset !== null) {
			$offset = intval($offset);
			$sql .= $length === null ? " LIMIT {$offset}" : " LIMIT {$offset}," . intval($length);
		}
		return $sql;
	}

	/**
	 * select and return an array
	 * @param string $table
	 * @param string $condition
	 * @param array $fields
	 * @param string $order (eg. 'id DESC')
	 * @param int $offset limit offset
	 * @param int $length limit size
	 * @return array
	 * @throws Exception
	 */
	public function get($table, $condition, array $fields = array(), $order = null, $offset = null, $length = null, $group = null, $having = null)
	{
		if (!$this->check_name($table)) {
			throw new Exception("table name invalid", Errno::PARAM_INVALID);
		}		
		$field_str = $this->get_field_str($fields);
		$condition = $condition ? " WHERE {$condition}" : '';
		$group_str = $this->get_group_str($group,$having);
		$order_str = $this->get_order_str($order);
		$sql = "SELECT {$field_str} FROM `{$table}`{$condition}{$group_str}{$order_str}";
		return $this->get_by_sql($sql, $offset, $length);
	}

	/**
	 * Select by sql and return array
	 * @param $sql
	 * @param null $offset
	 * @param null $length
	 * @return array
	 * @throws Exception
	 */
	public function get_by_sql($sql, $offset = null, $length = null)
	{
		$limit_sql = $this->get_limit_sql($offset,$length);
		$sql .= $limit_sql;

		$result = $this->execute($sql);
		if (!$result) {
			throw new Exception('execute sql failed,error:' . mysqli_error($this->db_link) . ',sql[' . $sql . ']', Errno::DB_FAILED);
		}

		$items = array();
		while ($item = mysqli_fetch_assoc($result)) {
			$items[] = $item;
		}

		mysqli_free_result($result);

		return $items;
	}

	/**
	 * Select by sql and return MysqlRecordSet
	 *
	 * @param string $sql
	 * @param int $offset
	 * @param int $length
	 *
	 * @return MysqlRecordSet
	 */
	public function get_record_set_by_sql($sql, $offset = null, $length = null)
	{
		$result = $this->execute($sql);
		return new MysqlRecordSet($result);
	}

	/**
	 * Count records by condition
	 *
	 * @param string $table
	 * @param string $condition
	 * @param string $field
	 *
	 * @return int
	 */
	public function count($table, $condition = '', $field = '*')
	{
		$condition = empty($condition) ? '' : ' WHERE ' . $condition;
		$sql = "SELECT COUNT({$field}) FROM `{$table}`{$condition}";

		$result = $this->execute($sql);
		$item = mysqli_fetch_row($result);
		mysqli_free_result($result);

		return intval($item[0]);
	}

	/**
	 *  Insert record
	 * @param $table
	 * @param array $record
	 * @return boolean
	 * @throws Exception
	 */
	public function insert($table, array $record)
	{
		$insert = $this->build_insert_string($record);
		$insert_fields = $insert['fields'];
		$insert_values = $insert['values'];
		$sql = "INSERT INTO `$table`($insert_fields) VALUES($insert_values)";
		return $this->execute($sql);
	}

	/**
	 * insert or update record
	 * ffected on MySQL 4.1 +, required primary key
	 * @param $table
	 * @param array $insert_record
	 * @param array $update_record
	 * @return boolean
	 * @throws Exception
	 */
	public function insert_or_update($table, array $insert_record, array $update_record)
	{
		$insert = $this->build_insert_string($insert_record);
		$insert_fields = $insert['fields'];
		$insert_values = $insert['values'];
		$update_items = $this->build_update_string($update_record);
		$sql = "INSERT INTO `{$table}`({$insert_fields}) VALUES({$insert_values}) ON DUPLICATE KEY UPDATE {$update_items}";
		return $this->execute($sql);
	}

	/**
	 * @param $table
	 * @param $record
	 * @param string $condition
	 * @return boolean
	 * @throws Exception
	 */
	public function update($table, $record, $condition = '')
	{
		if (empty($condition)) {
			throw new Exception('condition is empty', Errno::PARAM_INVALID);
		}
		$update_string = $this->build_update_string($record);
		$sql = "UPDATE `{$table}` SET {$update_string} WHERE {$condition}";
		return $this->execute($sql);
	}

	/**
	 * Delete
	 * @param $table
	 * @param $condition
	 * @return bool|\mysqli_result
	 * @throws Exception
	 */
	public function delete($table, $condition)
	{
		if (empty($condition)) {
			throw new Exception('condition is empty', Errno::PARAM_INVALID);
		}

		$sql = "DELETE FROM `$table` WHERE $condition";
		return $this->execute($sql);
	}

	/**
	 * get last insert id
	 *
	 * @return int
	 */
	public function get_insert_id()
	{
		return mysqli_insert_id($this->db_link);
	}

	/**
	 * get affected rows
	 *
	 * @return int
	 */
	public function get_affected_rows()
	{
		return mysqli_affected_rows($this->db_link);
	}

	/**
	 * transaction start
	 *
	 * @return void
	 */
	public function start_transaction()
	{
		$this->execute('start transaction');
	}

	/**
	 * transaction commit
	 *
	 * @throw Exception
	 * @return void
	 */
	public function commit()
	{
		$this->execute('commit');
	}

	/**
	 * transaction rollback
	 *
	 * @return void
	 */
	public function rollback()
	{
		$this->execute('rollback');
	}

	/**
	 * get error code
	 * @return string
	 */
	public function get_errno()
	{
		if ($this->db_link) {
			return mysqli_errno($this->db_link);
		}

		return null;
	}

	/**
	 * get error message
	 * @return string
	 */
	public function get_error()
	{
		if ($this->db_link) {
			return mysqli_error($this->db_link);
		}

		return null;
	}

	/**
	 * escape sql string
	 * @param string $var
	 * @return string
	 */
	public function escape($var)
	{
		$this->open();
		if ($var instanceof Expression) {
			return $var->getSql($this);
		} else {
			if (is_numeric($var)) {
				return $var;
			} else {
				return mysqli_real_escape_string($this->db_link, $var);
			}
		}
	}

	/**
	 * escape array
	 * @param array $items
	 * @return array
	 */
	public function escape_array($items)
	{
		$this->open();
		foreach ($items as $k => $v) {
			$items[$k] = $this->escape($v);
		}
		return $items;
	}

	public function build_insert_string($item)
	{
		$fields = array();
		$values = array();
		foreach ($item as $k => $v) {
			if (!$this->check_name($k)) {
				throw new Exception("field \"{$k}\" invalid", Errno::PARAM_INVALID);
			}
			if ($v instanceof Expression) {
				$val = $v->getSql($this);
			} else {
				if (is_int($v) || is_float($v)) {
					$val = $v;
				} else {
					$val = "'" . $this->escape($v) . "'";
				}
			}
			$fields[] = $k;
			$values[] = $val;
		}
		$fields = '`' . implode('`,`', $fields) . '`';
		$values = implode(',', $values);
		return array(
			'fields' => $fields,
			'values' => $values,
		);
	}

	public function build_update_string($item)
	{
		$tmp = array();
		foreach ($item as $k => $v) {
			if (!$this->check_name($k)) {
				throw new Exception("field \"{$k}\" invalid", Errno::PARAM_INVALID);
			}
			if ($v instanceof Expression) {
				$val = $v->getSql($this);
			} else {
				if (is_int($v) || is_float($v)) {
					$val = $v;
				} else {
					$val = "'" . $this->escape($v) . "'";
				}
			}
			$tmp[] = "`{$k}`=" . $val;
		}
		return implode(',', $tmp);
	}

	/**
	 * check table name or field name
	 * @param string|mix $string
	 * @return boolean
	 */
	public function check_name($string)
	{
		if ($string instanceof Expression) {
			return true;
		}
		if (preg_match('/^[a-z0-9_]+$/i', $string)) {
			return true;
		}
		return false;
	}

	public function get_field_name($field)
	{
		if ($field instanceof Expression) {
			return $field->getSql($this);
		} else {
			return '`' . $field . '`';
		}
	}

	/**
	 * check table name or field name
	 * @param string| array $field
	 * @return bool
	 */
	public static function check_field($field)
	{
		$pattern = '/^[a-z0-9_]+$/i';
		if (is_string($field)) {
			if (preg_match($pattern, $field)) {
				return true;
			} else {
				return false;
			}
		}
		if (!is_array($field)) {
			return true;
		}
		foreach ($field as $f) {
			if (!preg_match('', $f)) {
				return false;
			}
		}
		return false;
	}

	/**
	 * format sql in a safty way
	 *
	 * @param string $format %bcdeufFosxXn
	 * @param string $argN
	 *
	 * @return string
	 */
	public function format_sql($format, $arg1, $argN)
	{
		if (empty($format)) {
			throw new Exception('format string is empty', Errno::PARAM_INVALID);
		}

		$args = func_get_args();
		array_shift($args);

		$valid_exp = '/(?:%%|%(?:[0-9]+\$)?[+-]?(?:[ 0]|\'.)?-?[0-9]*(?:\.[0-9]+)?([bcdeufFosxXn]))/';
		if (!preg_match_all($valid_exp, $format, $matches)) {
			throw new Exception('format_sql() failed,no types in input', Errno::PARAM_INVALID);
		}
		$types = array();
		foreach ($matches[1] as $val) {
			if ($val !== '') {
				$types[] = $val;
			}
		}

		if (count($types) != count($args)) {
			throw new Exception('_format_sql() failed,wrong parameter count', Errno::PARAM_INVALID);
		}

		$i = 0;
		foreach ($types as $k => $type) {
			switch ($type) {
					//int
				case 'b':
				case 'c':
				case 'd': //int
				case 'u':
				case 'o':
				case 'x':
				case 'X':
					if (!is_numeric($args[$i]) || is_float($args[$i])) {
						throw new Exception("format_sql() failed,value '{$args[$i]}' not match the type '%{$type}'.", Errno::PARAM_INVALID);
					}
					break;

					//int or float
				case 'e':
				case 'f':
				case 'F':
					if (!is_numeric($args[$i])) {
						throw new Exception("format_sql() failed,value '{$args[$i]}' not match the type '%{$type}'.", Errno::PARAM_INVALID);
					}
					break;

					//string,need escape
				case 's':
					$args[$i] = $this->escape($args[$i]);
					break;

					//table or field name,need check
				case 'n':
					if (!$this->check_name($args[$i])) {
						throw new Exception("format_sql() failed,value '{$args[$i]}' not match the type '%{$type}'.", Errno::PARAM_INVALID);
					}

					//set to 's',so it is can deal by vsprintf
					$types[$k] = 's';
					$format = str_replace('%n', '%s', $format);
					break;

					//remove the %%
				case '':
					unset($types[$k]);
					break;

				default:
					throw new Exception("format_sql() failed,type '{$type}' is invalid", Errno::PARAM_INVALID);
			}

			$i++;
		}

		return vsprintf($format, $args);
	}
}

/**
 * Record Set
 */
class MysqlRecordSet
{
	/**
	 * mysqli_result
	 * @var \mysqli_result
	 */
	private $_result;

	/**
	 * init
	 * @param $result
	 */
	public function __construct($result)
	{
		$this->_result = $result;
	}

	public function __destruct()
	{
		$this->_result && mysqli_free_result($this->_result);
	}

	/**
	 * next record
	 *
	 * @return array
	 */
	function next()
	{
		return mysqli_fetch_assoc($this->_result);
	}

	/**
	 * seek to the records pos
	 *
	 * @param int $offset
	 *
	 * @return boolean
	 */
	function seek($offset)
	{
		return mysqli_data_seek($this->_result, $offset);
	}
}
